﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data ;
using System.Data .SqlClient ;
using dhDataAcess.dataAcess .sysDataSetTableAdapters ;
using dhDataAcess.Model ;

namespace dhDataAcess.sysDAL
{
   public class userListDAL
    {
       string tableName = "userList";
        private SqlTransaction tran = null;
        private SqlConnection conn = null;
        public userListDAL(SqlTransaction _transaction, SqlConnection _conn)
        {
            tran = _transaction;
            conn=_conn;
        }
      
        /// <summary>
        /// 插入一行用户表数据
        /// </summary>
        /// <param name="deptRow"></param>
        /// <returns></returns>

        public int userListInsert(userListModel deptRow)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            string insertStr = baseBLL.dTableColumnRead.insertSqlStr(tableName).ToString();
            //string insertStr = "insert into userList (mark,updateUser,updateDate,createUser,createDate,pwd,groupNum,workerNum,userNum) values(@mark,@updateUser,@updateDate,@createUser,@createDate,@pwd,@groupNum,@workerNum,@userNum)";

            SqlParameter[] para = {
                                  new SqlParameter ("@mark",deptRow .mark ),
                                  new SqlParameter ("@updateUser",deptRow .updateUser),
                                  new SqlParameter ("@updateDate",deptRow .updateDate),
                                  new SqlParameter ("@createUser",deptRow .createUser),
                                  new SqlParameter ("@createDate",deptRow .createDate),
                                  new SqlParameter ("@pwd",deptRow .pwd ),
                                  new SqlParameter ("@groupNum",deptRow .groupNum),
                                  new SqlParameter ("@workerNum",deptRow .workerNum ),
                                  new SqlParameter ("@userNum",deptRow.userNum)
                                  };

            cmd.Parameters.AddRange(para);
            cmd.CommandText = insertStr;
            cmd.Transaction = tran;
            int i = cmd.ExecuteNonQuery();
            return i;
        }

        /// <summary>
        /// 更新一行用户表数据
        /// </summary>
        /// <param name="deptRow"></param>
        /// <returns></returns>
 
        public int userListUpdate(userListModel deptRow)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            string updateStr = baseBLL.dTableColumnRead.updateSqlStr(tableName).ToString();
            //string updateStr = "update userList set mark=@mark,updateUser=@updateUser,updateDate=@updateDate,createUser=@createUser,createDate=@createDate,pwd=@pwd,groupNum=@groupNum,workerNum=@workerNum where userNum=@userNum";


            SqlParameter[] para = {
                                  new SqlParameter ("@mark",deptRow .mark ),
                                  new SqlParameter ("@updateUser",deptRow .updateUser),
                                  new SqlParameter ("@updateDate",deptRow .updateDate),
                                  new SqlParameter ("@createUser",deptRow .createUser),
                                  new SqlParameter ("@createDate",deptRow .createDate),
                                  new SqlParameter ("@pwd",deptRow .pwd ),
                                  new SqlParameter ("@groupNum",deptRow .groupNum),
                                  new SqlParameter ("@workerNum",deptRow .workerNum ),
                                  new SqlParameter ("@userNum",deptRow.userNum)
                                  };
           
            cmd.Parameters.AddRange(para);
            cmd.CommandText = updateStr;
            cmd.Transaction = tran;
            int i = cmd.ExecuteNonQuery();
            return i;
        }


        /// <summary>
        /// 删除一行用户表数据
        /// </summary>
        /// <param name="userNum"></param>
        /// <returns></returns>
 
        public int userListDel(string userNum)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            string deleteStr = baseBLL.dTableColumnRead.delSqlStr(tableName).ToString();
            //string deleteStr = "delete userList where userNum=@userNum";
            SqlParameter[] para = {
                                new SqlParameter ("@userNum",userNum)
                              };

            cmd.Parameters.AddRange(para);
            cmd.CommandText = deleteStr;
            cmd.Transaction = tran;
            int i = cmd.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 根据用户编号返回工号
        /// </summary>
        /// <param name="userNum"></param>
        /// <returns></returns>

        public string userListworkerNum(string userNum)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            string selectStr = "selete workerNum from userList where userNum=@userNum";
            SqlParameter[] para = {
                                new SqlParameter ("@userNum",userNum),
                              };

            cmd.Parameters.AddRange(para);
            cmd.CommandText = selectStr;
            cmd.Transaction = tran;
            SqlDataReader rdr = cmd.ExecuteReader();
            string workerNum = null;
            while (rdr.Read())
            {
                workerNum = rdr[0].ToString();
            }

            return workerNum;
        }
    }
}
